﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace studentmanagementdemo
{
    public partial class frmxeplop : Form
    {
        private SqlConnection con;
        private DataTable dt = new DataTable("tblStudent");
        private DataTable dtClass = new DataTable("tblClass");
        private SqlDataAdapter da = new SqlDataAdapter();
        private Boolean kt;

        private void connect()
        {
            //String cn = "Data Source=127.0.0.1;Initial Catalog=iPMAC;Persist Security Info=True;User ID=sa;Password=sa";
            String cn = "Data Source=(local);Initial Catalog=iPMAC;Integrated Security=True";
            try
            {
                con = new SqlConnection(cn);
                con.Open(); //Moi ket noi                
            }
            catch (Exception ex)
            {
                MessageBox.Show("Không thể kết nối tới cơ sở dữ liệu !", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void disconnect()
        {
            con.Close(); //Dong ket noi
            con.Dispose(); //Giai phong tai nguyen
            con = null; //Huy doi tuong
        }

        private void getdata()
        {
            SqlCommand command = new SqlCommand(); //Khai bao 1 command
            command.Connection = con; //Ket noi
            command.CommandType = CommandType.Text; //Khai bao kieu command
            command.CommandText = @"Select 
                                        tblStudent.fldID as N'MaSV', 
                                        (fldFirstName + ' ' + fldLastName) as N'HoTenSV', 
                                        fldAge as N'Tuoi', 
                                        (Case fldSex when 'True' then N'Nam' 
                                            when 'False' then N'Nữ' 
                                            else N'Chưa xác định' end) as N'GioiTinh',  
                                        fldCreatedDate as N'CreatedDate', 
                                        IsNULL(fldClassName, N'Chưa học') as N'TenLop',
                                        fldClassID as N'ClassID' 
                                  from tblStudent 
                                        left outer join tblClass 
                                        on tblStudent.fldClassID = tblClass.fldID"; //Cau truy van SQL
            da.SelectCommand = command; //Gan command cho da            
            da.Fill(dt); //Nap du lieu vao table
            grvxeplop.DataSource = dt; //Load du lieu len DataGridView 

            cbohotensv.DataSource = dt;
            cbohotensv.DisplayMember = "HoTenSV";
            cbohotensv.ValueMember = "MaSV";

            command.CommandText = "Select * From tblClass";
            da.SelectCommand = command;
            da.Fill(dtClass);
            cbolop.DataSource = dtClass;
            cbolop.DisplayMember = "fldClassName";
            cbolop.ValueMember = "fldID";
        }

        public frmxeplop()
        {
            InitializeComponent();
        }

        private void frmxeplop_Load(object sender, EventArgs e)
        {
            connect();
            getdata();
            cbolop.DataBindings.Add("Text", grvxeplop.DataSource, "TenLop");
        }

        private void btnthoat_Click(object sender, EventArgs e)
        {
            Close();
            Dispose();
            disconnect();
        }

        private bool kiemtra()
        {
            kt = true;
            if (cbohotensv.Text == "")
            {
                MessageBox.Show("Bạn chưa chọn Sinh viên của SV !", "THÔNG BÁO", MessageBoxButtons.OK);
                kt = false;
            }
            else if (cbolop.Text == "")
            {
                MessageBox.Show("Bạn chưa chọn Lớp !", "THÔNG BÁO", MessageBoxButtons.OK);
                kt = false;
            }
            return kt;
        }

        private void btnxeplop_Click(object sender, EventArgs e)
        {
            kiemtra();
            if (kt == true)
            {
                DataRow row = dt.Select("MaSV = " + Convert.ToInt32(cbohotensv.SelectedValue))[0];
                row.BeginEdit();
                row["ClassID"] = cbolop.SelectedValue;
                row.EndEdit();
                SqlCommand commandUpdate = new SqlCommand();
                commandUpdate.Connection = con;
                commandUpdate.CommandType = CommandType.Text;
                commandUpdate.CommandText = @"Update tblStudent Set fldClassID=@fldClassID
                                                                    Where fldID = @fldID";
                commandUpdate.Parameters.Add("@fldID", SqlDbType.Int, 50, "MaSV");
                commandUpdate.Parameters.Add("@fldClassID", SqlDbType.Int, 50, "ClassID");
                da.UpdateCommand = commandUpdate;
                da.Update(dt);
                MessageBox.Show("Bạn đã xếp lớp cho sinh viên thành công !", "THÔNG BÁO", MessageBoxButtons.OK);
            }
        }
    }
}
